/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.andersen.database.dao;

import com.andersen.database.datatable.TableParam;
import com.andersen.database.entity.BuyingEntity;
import com.andersen.database.entity.EmployeeEntity;
import com.andersen.database.entity.PaymentEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Component;

/**
 *
 * @author Corvin
 */
@Component
public class PaymentDao extends AbstractDao{

     private static final String[] fields = {"buying_description", "payment_sum", "owner_name", "payment_date", "paid"};

    public static final int PAYING_FILTER_MODE_GET_BY_DATE_SPAN = 0;
    public static final int PAYING_FILTER_MODE_GET_CURRENT = 1;
    public static final int PAYING_FILTER_MODE_GET_LATE = 2;

     private PaymentEntity mapResultSetRow(ResultSet rSet) throws SQLException {
         return mapResultSetRow(rSet, false, false);
     }
    
     private PaymentEntity mapResultSetRow(ResultSet rSet, boolean hasBuying, boolean hasEmployee) throws SQLException {
         PaymentEntity result = new PaymentEntity();
         
         result.setId(rSet.getLong("id"));
         result.setBuyingId(rSet.getLong("buying_id"));
         result.setPaymentSum(rSet.getLong("payment_sum"));
         result.setPaymentDate(rSet.getDate("payment_date"));
         
         if (hasBuying) {
             BuyingEntity buying = new BuyingEntity();
             buying.setId(rSet.getLong("buying_id"));
             buying.setDescription(rSet.getString("buying_description"));
             buying.setFirmPricePart(rSet.getLong("buying_firm_price_part"));
             buying.setMonthPaySum(rSet.getLong("buying_month_pay_sum"));
             buying.setOwnerId(rSet.getLong("owner_id"));
             buying.setPrice(rSet.getLong("buying_price"));
             if (hasEmployee) {
                EmployeeEntity owner = new EmployeeEntity();
                owner.setId(rSet.getLong("owner_id"));
                owner.setName(rSet.getString("owner_name"));
                owner.setSurname(rSet.getString("owner_surname"));
                owner.setPatronymic(rSet.getString("owner_patronymic"));
                owner.setPosition(rSet.getString("owner_position"));
                owner.setSkype(rSet.getString("owner_skype"));
                owner.setPhone(rSet.getString("owner_phone"));
                owner.setOfficeId(rSet.getLong("owner_office_id"));
                owner.setDischarged(rSet.getBoolean("owner_discharged"));
                buying.setOwner(owner);
            }
            result.setBuying(buying);
         }
         
         return result;
    }
    
    public PaymentEntity getById(long id) {
        
        String sql = "SELECT * FROM payment WHERE id = ?;";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        
        PaymentEntity result = null;
        
        try{

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            if(resultSet.first())
                result = mapResultSetRow(resultSet);
        } catch(SQLException exc) {
            LOG.error("Error paymentDao.getById()", exc);
        } finally {
            safeClose(resultSet);
            safeClose(statement);
            safeClose(connection);
        }
        
        return result;
    }
    
    public long insert(PaymentEntity payment) {
        
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO payment(");
        sql.append("buying_id, ");
        sql.append("payment_sum, ");
        sql.append("payment_date) ");
        sql.append("VALUE(?,?,?);");
                
        long resultId = ID_INVALID;
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setLong(1, payment.getBuyingId());
            preparedStatement.setLong(2, payment.getPaymentSum());
            preparedStatement.setDate(3, payment.getPaymentDate());
            if (preparedStatement.executeUpdate() > 0) {
                resultSet = preparedStatement.getGeneratedKeys();
                resultSet.first();
                resultId = resultSet.getLong(1);
            }
        
        } catch (SQLException exc) {
            LOG.error("PaymentDao.insert() error", exc);
        } finally {
            safeClose(resultSet);
            safeClose(preparedStatement);
            safeClose(connection);
        }
        
        
        return resultId;
        
    }
    
    public boolean update(PaymentEntity payment) {
        boolean ok = false;

        StringBuilder sql = new StringBuilder();

        sql.append("UPDATE payment ");
            sql.append("SET payment_sum = ?, ");
            sql.append("payment_date = ? ");
        sql.append("WHERE id = ?;");

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = dataSource.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setLong(1, payment.getPaymentSum());
            st.setDate(2, payment.getPaymentDate());
            st.setLong(3, payment.getId());
            if (st.executeUpdate() == 0) {
                LOG.error("Error with PaymentDao.update() - no rows updated.");
            } else {
                ok = true;
            }
        } catch(SQLException exc) {
            LOG.error("Error with PaymentDao.update()", exc);
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return ok;
    }

    public List<PaymentEntity> getPayments(TableParam param, int filteringMode) {

        StringBuilder sql = getPaymentTableSQL();

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        List<PaymentEntity> result = null;

        try {

            conn = dataSource.getConnection();

            switch (filteringMode) {
                case PAYING_FILTER_MODE_GET_BY_DATE_SPAN:
                    st = createDateSpanStatement(param, conn, sql);
                    break;
                case PAYING_FILTER_MODE_GET_CURRENT:
                    st = createCurrentStatement(param, conn, sql);
                    break;
                case PAYING_FILTER_MODE_GET_LATE:
                    st = createLateStatement(param, conn, sql);
                    break;
            }

            rs = st.executeQuery();
            result = new ArrayList<PaymentEntity>();

            while(rs.next()) {
                PaymentEntity payment = mapResultSetRow(rs, true, true);
                result.add(payment);
            }


        } catch (SQLException e) {
            LOG.error("PaymentDao.getPaymentsByDateSpan() error",e);
        } finally {
            safeClose(rs);
            safeClose(st);
            safeClose(conn);
        }
        return result;
    }

    private PreparedStatement createLateStatement(TableParam param, Connection conn, StringBuilder sql) throws SQLException{
        sql.append("WHERE p.payment_date < ? AND p.paid = FALSE ");

        appendSortOrder(sql, fields[param.getSortColumnNumber()], param.isSortAsc());
        appendPaginationParams(sql, param.getOffset(), param.getCount());
        sql.append(";");

        PreparedStatement st = conn.prepareStatement(sql.toString());

        Date date = new Date(Calendar.getInstance().getTimeInMillis());

        st.setDate(1, date);

        return st;
    }


    private PreparedStatement createCurrentStatement(TableParam param, Connection conn, StringBuilder sql) throws SQLException {
        sql.append("WHERE p.payment_date < ? AND p.payment_date > ? AND p.paid = FALSE ");

        appendSortOrder(sql, fields[param.getSortColumnNumber()], param.isSortAsc());
        appendPaginationParams(sql, param.getOffset(), param.getCount());
        sql.append(";");

        PreparedStatement st = conn.prepareStatement(sql.toString());

        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DATE, 25); //25th of current month
        Date fromDate = new Date(cal.getTimeInMillis());
        cal.add(Calendar.MONTH, 1); //25th of next month
        Date toDate = new Date(cal.getTimeInMillis());

        st.setDate(1, toDate);
        st.setDate(2, fromDate);

        return st;
    }



    private PreparedStatement createDateSpanStatement(TableParam param, Connection conn, StringBuilder sql) throws SQLException {
        sql.append("WHERE p.payment_date < ? AND p.payment_date > ? ");

        appendSortOrder(sql, fields[param.getSortColumnNumber()], param.isSortAsc());
        appendPaginationParams(sql, param.getOffset(), param.getCount());
        sql.append(";");

        PreparedStatement st = conn.prepareStatement(sql.toString());

        st.setDate(1, (Date)param.getFilterParams().get(param.FILTER_TO_DATE));
        st.setDate(2, (Date)param.getFilterParams().get(param.FILTER_FROM_DATE));

        return st;
    }


    private StringBuilder getPaymentTableSQL() {
        StringBuilder sql = new StringBuilder();

        sql.append("SELECT p.id AS id, ");
        sql.append("p.buying_id AS buying_id, ");
        sql.append("p.payment_sum AS payment_sum, ");
        sql.append("p.payment_date AS payment_date, ");
        sql.append("p.paid AS paid, ");
        sql.append("b.price AS buying_price, ");
        sql.append("b.month_pay_sum AS buying_month_pay_sum, ");
        sql.append("b.firm_price_part AS buying_firm_price_part, ");
        sql.append("b.owner_id AS owner_id, ");
        sql.append("b.description AS buying_description, ");
        sql.append("e.name AS owner_name, ");
        sql.append("e.surname AS owner_surname, ");
        sql.append("e.patronymic AS owner_patronymic, ");
        sql.append("e.`position` AS owner_position, ");
        sql.append("e.skype AS owner_skype, ");
        sql.append("e.phone AS owner_phone, ");
        sql.append("e.office_id AS owner_office_id, ");
        sql.append("e.discharged AS owner_discharged ");
        sql.append("FROM payment p JOIN buying b ON p.bying_id = b.id JOIN employee e ON b.owner_id = e.id ");

        return sql;
    }

    
}
